package com.lanqiao.guimei.dao;

import com.lanqiao.guimei.commons.JdbcUtil;
import com.lanqiao.guimei.commons.ResultSetHandler;
import com.lanqiao.guimei.vo.Page;
import com.lanqiao.guimei.vo.Users;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import org.apache.commons.lang3.StringUtils;
import org.apache.log4j.Logger;

import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * @author 韩原
 * @date 2019/8/13  19:16
 */
public class UsersDao extends BaseDao{
    Logger logger = Logger.getLogger(UsersDao.class);
    /**
     * 统计总记录数
     *
     * @param page
     * @return
     */
    public int countForPage(Page<Users> page ) {
        Users users =page.getQueryObject();
        List params = new ArrayList();
        StringBuffer sql = new StringBuffer("select count(1) cnt from GUIMEI_USERS where 1=1 and (gender='男' or gender='女')");
        // 生成查询条件，且将占位符对应位置的值存入列表
        //用户Id
        if (users.getUserId() != null){
            sql.append(" and user_id=? ");
            params.add(users.getUserId());
        }
        //用户名
        if (StringUtils.isNoneBlank(users.getLoginName())){
            sql.append(" and login_name like ?");
            params.add("%" +users.getLoginName()+ "%");
        }
        //用户姓氏
        if (StringUtils.isNoneBlank(users.getFirstName())){
            sql.append(" and First_name like ?");
            params.add("%" +users.getFirstName()+ "%");
        }
        //用户名字
        if (StringUtils.isNoneBlank(users.getLastName())){
            sql.append(" and Last_name like ?");
            params.add("%" +users.getLastName()+ "%");
        }
        //用户邮箱
        if (StringUtils.isNoneBlank(users.getEmail())){
            sql.append(" and email like ?");
            params.add("%" +users.getEmail()+ "%");
        }


        try  (Connection conn = JdbcUtil.getConnection();
              PreparedStatement ps = conn.prepareStatement(sql.toString());
        ) {
            for (int i = 0; i < params.size(); i++) {
                // 设置占位符的值
                ps.setObject(i+1, params.get(i));
            }
            ResultSet resultSet = ps.executeQuery();
            while (resultSet.next()) {
                // 查询结果只有一列，指定列索引
                return resultSet.getInt(1);
            }
        } catch (SQLException e) {
            logger.error("统计总记录数. ", e);
        }
        return 0;
    }
    /**
     * 分页
     * @param page
     * @return
     */
    public List<Users> findForPage(Page<Users> page) {
        Users users =page.getQueryObject();
        System.out.println(users);
        List params = new ArrayList();
        StringBuffer sql = new StringBuffer("select * from(" +
                "   select t.*,ROWNUM rn from (" +
                "       select user_id userId,login_name loginName,password," +
                "first_name firstName,last_name lastName,email,gender,header,hobbit," +
                "birthday from  GUIMEI_USERS where 1=1 ");
        // 生成查询条件，且将占位符对应位置的值存入列表
        //用户Id
        if (users.getUserId() != null){
            sql.append(" and user_id=? ");
            params.add(users.getUserId());
        }
        //用户名
        if (StringUtils.isNoneBlank(users.getLoginName())){
            sql.append(" and login_name like ?");
            params.add("%" +users.getLoginName()+ "%");
        }
        //用户姓氏
        if (StringUtils.isNoneBlank(users.getFirstName())){
            sql.append(" and First_name like ?");
            params.add("%" +users.getFirstName()+ "%");
        }
        //用户名字
        if (StringUtils.isNoneBlank(users.getLastName())){
            sql.append(" and Last_name like ?");
            params.add("%" +users.getLastName()+ "%");
        }
        //用户邮箱
        if (StringUtils.isNoneBlank(users.getEmail())){
            sql.append(" and email like ?");
            params.add("%" +users.getEmail()+ "%");
        }
        sql.append("and (gender='男' or gender='女') order by user_id");


        sql.append(") t where ROWNUM<=? " +
                ") where rn>=?");
        params.add(page.getLastIndex());
        params.add(page.getFirstIndex());
        System.out.println(params);
        System.out.println(sql.toString());
        try  (Connection conn = JdbcUtil.getConnection();
              PreparedStatement ps = conn.prepareStatement(sql.toString());
        ){
            for (int i = 0; i < params.size(); i++) {
                // 设置占位符的值
                System.out.println(params.get(i));
                ps.setObject(i+1, params.get(i));
            }

            ResultSet rs = ps.executeQuery();
            List<Users> users1 = new ResultSetHandler<Users>().toBeanList(rs, Users.class);

            return users1;
        } catch (SQLException e) {
            logger.error("分页查询失败。", e);
        }
        return null;
    }

    /**
     * 删除
     * @param
     * @return
     */
    public boolean deleteuser(Users users){
        String sql = "update guimei_users set password=dbms_random.string('P',5 ),first_name=null," +
                "last_name =null,email=null,gender=null,header=null,hobbit=null,birthday=null " +
                "where user_id=?";

        try (Connection conn = JdbcUtil.getConnection();
             PreparedStatement ps = conn.prepareStatement(sql);
        ) {
            ps.setInt(1,users.getUserId());
            int deleted = ps.executeUpdate();
            return (deleted==1 ? true : false);
        } catch (SQLException e) {
            System.err.println("删除员工失败：" + e.getMessage());
        }
        return false;
    }

    /**
     * 更新
     * @param users
     * @return
     */
    public Boolean userupdata(Users users){
        String sql ="update guimei_users set password = 123456 where user_id = ?";
        try{
            int userupda=runner.update(sql,users.getUserId());
            return (userupda==1 ? true : false);
        }catch (SQLException e) {
            e.printStackTrace();
        }
        return false;
    }


    /*
     * 登录
     *
     * */
    public Users login(Users user){
        //登陆方法
        //System.out.println("......"+user);

        /**private String userId;      //会员号
         private String loginName;   //登陆账号
         private String password;   //登陆密码
         private String firstName;   //名字
         private String lastName;    //姓氏
         private String email;       //邮箱
         private String gender;      //性别
         private String header;      //头像
         private String hobbit;      //爱好
         private Date birthday;      //生日
         600000000
         */
        String sql = "select USER_ID userId , LOGIN_NAME loginName , PASSWORD , " +
                "FIRST_NAME firstName , LAST_NAME lastName , EMAIL , GENDER , HEADER ," +
                " HOBBIT FROM GUIMEI_USERS WHERE LOGIN_NAME =? and PASSWORD =?";

        try (Connection conn = JdbcUtil.getConnection();
             PreparedStatement ps = conn.prepareStatement(sql);
        ){
            //设置占位符的值
            ps.setString(1 , user.getLoginName());
            ps.setString(2 , user.getPassword());

            //执行语句
            ResultSet rs = ps.executeQuery();
            ResultSetHandler<Users> rsh = new ResultSetHandler<Users>();
            Users users = rsh.toBean(rs,Users.class);
            return users;
        } catch (SQLException e) {
            System.err.println("登陆失败："+e.getMessage());
            e.printStackTrace();
        }
        return null;
    }

    /*
     * 注册 **************************************************************************************************************
     * */
    public boolean register(Users newUser){
        /*
        * --插入一行数据
            insert into demo values(1,'abc','ABC');
        **/
        String sql = "INSERT INTO GUIMEI_USERS VALUES (USER_SEQ.nextval,?,?,?,?,?,?,?,?,?)";

        try (Connection conn = JdbcUtil.getConnection();
        ){
            int row = runner.update(conn, sql, newUser.getLoginName(), newUser.getPassword(),
                    newUser.getFirstName(), newUser.getLastName(),
                    newUser.getEmail(), newUser.getGender(), newUser.getHeader(),
                    newUser.getHobbit(), newUser.getBirthday());
            return (row==1?true:false);

        } catch (SQLException e) {
            e.printStackTrace();
        }
        return false;
    }

}
